#########################
#   Author : leilei.Qin #
#   Date : 2025/06/24   #
#   Id : SM2892         #
#########################

# 报表-折扣明细
from datetime import datetime
import pandas as pd
from dbResps import dc
from dbResps import storeId, Log
import json


def getQueryEmployPerformanceReportFromDB(startTime:int, endTime:int):
    getQueryEmployPerformanceReport = dict()
    # 查询order表
    orderTableName = 'order'
    orderColumns = " cash_user_id as employId, cash_user_name as employName, actual_amount as actualAmount, " \
                   "settle_time as settleTime, discount_amount as discountAmount, tip_amount as tipAmount, payable_amount as payableAmount"
    orderWhere = f'store_id = "{storeId}" and `status` = \"SETTLED\" and (UNIX_TIMESTAMP(settle_time)*1000 between {startTime} and {endTime})'
    orderResp = dc.select(tableName=orderTableName, where=orderWhere, columns=orderColumns)
    EmployPerformance = list()
    if len(orderResp) > 0:
        for item in orderResp:
            settleTime = item['settleTime'].strftime("%Y-%m-%d")
            employ = f"{item['employName']}_{item['employId']}_{settleTime}"
            EmployPerformance.append({
                "employ": employ,
                "discountAmount":abs(item['discountAmount']),
                "tipAmount":item['tipAmount'],
                "orderAmount":item['payableAmount'],
                "actualAmount":item['payableAmount']
            })
        df = pd.DataFrame(EmployPerformance)
        employDetail = df.groupby('employ').agg(
            {
                "orderAmount":"sum",
                "tipAmount":"sum",
                "discountAmount": "sum",
                "actualAmount":"sum",
             }
        ).reset_index()
        result = [{row['employ']: row.drop('employ').to_dict()} for _, row in employDetail.iterrows()]
        getQueryEmployPerformanceReport['employDetail'] = result
    return getQueryEmployPerformanceReport

def getHistoryEmployPerformanceReportFromDB(startTime:int, endTime:int):
    getHistoryEmployPerformanceReport = dict()
    # 查询order表
    orderTableName = 'employee_performance_report_hour'
    orderColumns = "report_day as reportDay, employee_id as employId, employee_name as employName, actual_amount as actualAmount, " \
                   "discount_amount as discountAmount, tip_amount as tipAmount, order_amount as orderAmount"
    startTime2Date = datetime.strptime(dc.timestamp2Data(startTime), "%Y/%m/%d %H:%M:%S").strftime('%Y-%m-%d %H:%M:%S')
    endTime2Date = datetime.strptime(dc.timestamp2Data(endTime), "%Y/%m/%d %H:%M:%S").strftime('%Y-%m-%d %H:%M:%S')
    orderWhere = f'store_id = "{storeId}" and report_day between "{startTime2Date}" and "{endTime2Date}"'
    orderResp = dc.select(tableName=orderTableName, where=orderWhere, columns=orderColumns)
    HistoryEmployPerformance = list()
    if len(orderResp) > 0:
        for item in orderResp:
            reportDay = item['reportDay'].strftime("%Y-%m-%d")
            employ = f"{item['employName']}_{item['employId']}_{reportDay}"
            HistoryEmployPerformance.append({
                "employ":employ,
                "orderAmount": item['orderAmount'],
                "tipAmount": item['tipAmount'],
                "discountAmount": abs(item['discountAmount']),
                "actualAmount": item['actualAmount']
            })
        df = pd.DataFrame(HistoryEmployPerformance)
        employDetail = df.groupby('employ').agg(
            {
                "orderAmount": "sum",
                "tipAmount": "sum",
                "discountAmount": "sum",
                "actualAmount": "sum",
            }
        ).reset_index()
        result = [{row['employ']: row.drop('employ').to_dict()} for _, row in employDetail.iterrows()]
        getHistoryEmployPerformanceReport['employDetail'] = result
    return getHistoryEmployPerformanceReport

# startTime = 1718557200000
# endTime = 1718729999000

# startTime = 1719075600000
# endTime = 1719161999000
# getQueryEmployPerformanceReport = getQueryEmployPerformanceReportFromDB(startTime, endTime)
# print(getQueryEmployPerformanceReport)
# getHistoryEmployPerformanceReport = getHistoryEmployPerformanceReportFromDB(startTime, endTime)
# print(getHistoryEmployPerformanceReport)
